MYSQL
A database is your best choice for storing data in your web application, and the MySQL database server has always been the most popular choice among PHP developers. It's supported by almost any hosting company offering PHP, which makes it easy to get started with, and you can even download and install it on your own computer, for testing purposes.
MySQL uses the SQL (Structured Query Language) programming language to work with the data, and PHP interacts with MySQL by simply passing SQL code through a set of MySQL functions to the MySQL server, which then returns a result that PHP can interpret. It can seem a bit scary to have to learn a second language to interact with databases, but fortunately SQL is a fairly simple language, which looks a lot like the English language and we will provide you with some good SQL examples, allowing you to do the most common tasks.
In the following chapters we will work with the MySQL database and make it do various things for us. To do it properly, we need some common test data, which you will need to add to a database for which you have access to. The easiest way to do this is to use one of the many MySQL tools, with the most popular one being phpMyAdmin, which is installed on most servers offering PHP and MySQL. If you don't have access to phpMyAdmin, you can install it, use one of the many downloadable applications or use the MySQL prompt. Whatever you choose, you should execute the following SQL code against your database. In phpMyAdmin, this is done by clicking the button labelled "SQL":
CREATE TABLE `test_users` ( `id` int(11) NOT NULL auto_increment, `name` varchar(100) NOT NULL default '', `country` varchar(100) NOT NULL default '', PRIMARY KEY (`id`) ); INSERT INTO `test_users` VALUES (1,'David','USA'); INSERT INTO `test_users` VALUES (2,'Sammy','Canada'); INSERT INTO `test_users` VALUES (3,'Heidi','Germany'); INSERT INTO `test_users` VALUES (4,'Pierre','France'); INSERT INTO `test_users` VALUES (5,'Carlos','Spain');
When your done, a new table called "test_users" should have been created and filled with a small amount of testing data. In the next chapters we will work with it. If you're working with your own installation of PHP, please make sure that MySQL support has been enabled. This can be done in the php.ini file.
Establishing a connection
In the previous chapter, we created a database table with some test data. Now we need to establish a connection to the database server so that we can start working with the data. Connecting to a MySQL database with PHP is very easy. It can be done using a single function from the nice array of MySQL related functions in PHP:
mysql_connect("localhost", "username", "password");
If default values have been configured in your configuration file, you can leave out these parameters, but otherwise you will need to specify a host and typically a username and a password for it. Both the username and password has been set by yourself or by your hosting company. If you host MySQL on your own machine, or if you run your PHP code on your hosting company's server, you can usually just specify localhost as the host value. If in doubt, ask your hosting company or check their support pages.
The mysql_connect() function returns a resource, a direct link to the database server, which should be used to access the database server each time you use one of the MySQL functions. However, if this resource is not specified, PHP will just use the last opened connection, allowing you to write less code. In most cases you will only need one MySQL connection per page, so this should work just fine for you.
To work with a database, you need to call one more function, the mysql_select_db() function. The name really tells it all - for a specific connection, it selects a database that you will be working with. It's very simple to use:
mysql_select_db("my_database");
This should be called after you have established the connection using mysql_connect(). It takes one or two parameters. The first should be the name of the database you wish to use. The second one is optional and allows you to specify which MySQL resource link the function should be performed on. Here is an example where we use these two essential functions together:
mysql_connect("localhost", "username", "password"); mysql_select_db("my_database");
And here is the same example, but where we get the resource link from mysql_connect() and use it. This is the way you should do it mainly if you need to connect to more than one database server on the same page:
$dbConnection = mysql_connect("localhost", "username", "password"); mysql_select_db("my_database", $dbConnection);
With this, we now have a connection to the database server and we have selected the desired database. In the next chapter, we will use this connection, but before we do so, we should talk a bit about closing the connection again. A connection to a database server is costly, so it should obviously be closed once we're done using it. However, PHP can and will do this for us automatically, if we choose not to do it, once the page is fully executed. If we for some reason want to close a MySQL connection before the page is done executing, we can do it by using the mysql_close() function:
mysql_close();
This will close the last opened connection. If we want to close a specific connection, just pass its link to the function:
mysql_close($dbConnection);
Okay, with a good understanding of how a connection is started, we can now move on to using it. Read on.
Retrieving data
In the last chapter, we established a connection to your MySQL database, so by now its time to do something interestingly with the connection. As mentioned in the introduction, we interact with the MySQL server by sending SQL code through a PHP function, more specifically the mysql_query() function.
To retrieve data through SQL, the SELECT statement is used. It comes in many variations, but it its most simple form, it typically looks like this:
SELECT column_name FROM table_name
This query will select data from the column called "column_name" in the table called "table_name" and return all rows of it. In the introduction chapter, we inserted some rows of data in our test_users table, where we have the columns id, name and country. To get this data, our query could look like this:
SELECT id, name, country FROM test_users
That's the SQL needed to retrieve our data. Let's make PHP execute it for us, by using the mysql_query() function:
mysql_connect("localhost", "username", "password"); mysql_select_db("my_database"); $query = mysql_query("SELECT id, name, country FROM test_users");
The mysql_query() function simply sends the SQL code to the MySQL server and then returns a link to the result. We will then have to use one of the many related PHP functions to actually use the result. For instance, we can see how many rows were returned by using the mysql_num_rows() function (the connection should already be made, as shown above):
$query = mysql_query("SELECT id, name, country FROM test_users"); echo "The table currently contains " . mysql_num_rows($query) . " row(s)";
Of course, if we only wanted the number of rows, there would be more efficient ways of doing it and there would be no reason for selecting all three rows. Instead of just getting the number of rows, let's try actually getting some data out:
$query = mysql_query("SELECT id, name, country FROM test_users"); echo "The first name is: " . mysql_result($query, 0, "name");
The mysql_result() can pick out a single piece of data from a result link. As parameters, we specify the query link, the row index and the name of the column we want data for, so in this case we get a result from the $query result reference, we use the first row of returned (row number zero) and we get data from the column called "name". That will get us the first name in the table, which is then outputted.
In most situations you might need all the rows of data that you select, along with all the columns you select. This is usually done with the mysql_fetch_array() function, which simply gets you an entire row of data as an array, while moving the internal pointer one step ahead, so that the function will get you the row after that the next time you call it. This makes it excellent for using with a loop, to get all the rows one after another. Let's look at an example where we do just that:
$query = mysql_query("SELECT id, name, country FROM test_users"); while($row = mysql_fetch_array($query)) echo $row["name"] . " is from " . $row["country"] . "<br />";
We use a while loop, in which we assign the result of the mysql_fetch_array() function to the variable named $row on each iteration. This works because mysql_fetch_array() will return FALSE when there are no more rows, in which case the while loop will end. As long as there IS in fact a row left, mysql_fetch_array() places all columns and their values in the $row variable, where we can access it from simply by asking for the same name used in the SQL code. In our example, that gives us access to id, name and country, but to keep it less complicated, I have only used name and country so far.
So, that's the most basic ways of getting data out of the database. As you can see, it's fairly easy, since PHP has a bunch of nice functions for doing it, but there's much more to working with MySQL and there's definitely more to writing SQL. Read on to learn more about both.
MySQL and the WHERE part
In the previous chapter, we saw how we could use the SELECT query to get data from the database, but we weren't exactly very picky about which data to get. As you will realize in the following chapters, the SELECT statement is very powerful and allows you to do pick, prioritize and sort your data in all sorts of way before returning it. In this chapter we will have a look at the WHERE part, which allows you to decide which data to select.
An SQL statement with a WHERE part could look like this:
SELECT id, name, country FROM test_users WHERE id > 3
You can of course set more than one criteria:
SELECT id, name, country FROM test_users WHERE id > 3 AND id < 8
And you can use strings as criteria as well:
SELECT id, name, country FROM test_users WHERE country = 'USA'
You can even use MySQL's own functions in the WHERE part. For instance like this, where we use the SUBSTRING() function in MySQL to get all users starting with the letter "S":
SELECT id, name, country FROM test_users WHERE SUBSTRING(name, 1, 1) = 'S'
To test the examples above, you can use this test code we wrote in a previous chapter. Simply put one of the queries inside the mysql_query() function and run the code:
mysql_connect("localhost", "username", "password"); mysql_select_db("my_database"); $query = mysql_query("Insert your SQL query here"); while($row = mysql_fetch_array($query)) echo $row["name"] . " is from " . $row["country"] . "<br />";
MySQL and the ORDER BY part
In the previous chapter, we used the WHERE part of an SQL query to decide which rows we wanted to SELECT. In this chapter, we will look into sorting the result, to get the data out in a specific order. In MySQL, as in most other SQL dialects, the keyword used to sort data is ORDER BY. Let's jump straight to an example:
SELECT name, country FROM test_users ORDER BY name
The ORDER BY is followed by the column we wish to sort by. In this example, we get our users sorted alphabetically by their name. The default order is from small to big, or in the case of strings, from A to Z, known as ascending order. In other words, the above example could also look like this:
SELECT name, country FROM test_users ORDER BY name ASC
But since it's the default order, it's optional. If you want from big to small or Z to A, we want it in descending order, for which we use the DESC keyword:
SELECT name, country FROM test_users ORDER BY name DESC
In some cases, it can be very useful to sort by more than one column. For instance, you may want to sort by country first, and name secondly. The syntax is the same, you just separate the columns you wish to sort by with a comma, like this:
SELECT name, country FROM test_users ORDER BY country, name
In this case, users will be ordered by the name of their country first, and people from the same country will be ordered by their own name. You can even use different sorting directions for each of the columns, like this:
SELECT name, country FROM test_users ORDER BY country ASC, name DESC
To test the examples above, you can use this test code we wrote in a previous chapter. Simply put one of the queries inside the mysql_query() function and run the code:
mysql_connect("localhost", "username", "password"); mysql_select_db("my_database"); $query = mysql_query("Insert your SQL query here"); while($row = mysql_fetch_array($query)) echo $row["name"] . " is from " . $row["country"] . "<br />";
MySQL and the LIMIT part
Sometimes you only need a limited number of rows from a table, for instance because you want pagination, where data are divided into pages with a specific number of rows on each page. In MySQL, you can limit the amount of rows returned with the LIMIT keyword. Here's an example:
SELECT name, country FROM test_users LIMIT 3
This query will return the first 3 rows in the table, but in most cases, you would want to order the data, to get the first rows based on a specific sort order. Fortunately, we learned about the ORDER BY keyword in the previous chapter, so here's an example where we combine them:
SELECT name, country FROM test_users ORDER BY country LIMIT 3
This will give us the first 3 rows, but based on the country name instead of the default sorting order in the table, which is usually the primary key. The LIMIT keyword is followed by one or two parameters. If there's only one, it will be used to control the amount of rows returned, but if you specify two parameters, the first will be used to control the starting row, while the second parameter becomes the amount of rows returned. Here's an example:
SELECT name, country FROM test_users ORDER BY country LIMIT 1, 3
This query will get 3 rows, starting with the secondary row, ordered by country name. You might think that the first number should be 2 instead of 1, to start from the secondary row, but the LIMIT keyword is zero-index based, which means that counting starts from 0 instead of 1. So to start from the first row, you would specify 0 (which is of course the default value), 1 to start from the second row, 2 to start from the third row and so on.
To test the examples above, you can use this test code we wrote in a previous chapter. Simply put one of the queries inside the mysql_query() function and run the code:
mysql_connect("localhost", "username", "password"); mysql_select_db("my_database"); $query = mysql_query("Insert your SQL query here"); while($row = mysql_fetch_array($query)) echo $row["name"] . " is from " . $row["country"] . "<br />";
Handling MySQL errors
When using PHP and MySQL together, you will likely run into a situation where you've made some sort of error in your SQL query, like misspelling a column name or a keyword or something like that. By default, PHP will not show you exactly what the problem is, only that you wrote a query which is not entirely correct. Let's try writing a faulty query to see the response from PHP:
$query = mysql_query("SELECT id, namme FROM test_users"); while($row = mysql_fetch_array($query)) echo $row['id'] . " - " . $row["name"] . " is from " . $row["country"] . "<br />";
This is the example we are using a lot in this part of the tutorial, but in this case, we have misspelled the name column to provoke an error, which we get:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in test.php on line 7
As you can see, the error is not thrown until we try using the resource returned by the mysql_query() function, which we do when we call the mysql_fetch_array() function, in my file located on line 7. The error is very generic and not very helpful. This is on purpose, because knowledge about your database structure makes your website more vulnerable to SQL injection attacks, a problem we will discuss later on.
You might be able to spot the error and fix it in a lot of situations, but if not, you can use the mysql_error() function to get a bit more information abut the problem. This function simply returns any error returned from the last executed MySQL function. You should only use this function for finding and fixing problems, and then remove it again once the problem has been fixed. Here's the above example, but where we call the mysql_error() function to get more information:
$query = mysql_query("SELECT id, namme FROM test_users"); while($row = mysql_fetch_array($query)) echo $row['id'] . " - " . $row["name"] . " is from " . $row["country"] . "<br />"; echo mysql_error();
This will give you a far more useful error message:
Unknown column 'namme' in 'field list'
Try making various errors in the SQL query and see the message that MySQL returns through the mysql_error() function, to see how it responds. This will help you identify SQL errors better in the future. The above one is very precise and easy to fix, while others can be a bit more cryptic to the untrained eye